insert overwrite table jms_dm.dm_customer_date_network_level_ticket_detail_dt
select 3                                                             as networktype        --网点类型,3代理区,2加盟商,1网点
     , 1                                                             as timetype           --时间类型,区分1日,2周,3月
     , date_time                                                                           --日期
     , agent_code                                                                          --代理区名
     , agent_name                                                                          --代理区名
     , '--'                                                          as fran_code          --加盟商code
     , '--'                                                          as fran_name          --加盟商名
     , '--'                                                          as network_code       --寄件网点
     , '--'                                                          as network_name       --网点编码
     , customer_code                                                                       --客户code
     , customer_name                                                                       --客户名称
     , customer_create_time                                                                --客户签约时间
     , customer_create_time                                          as first_deliver_time --第一次发货时间
     , case
           when ticket_quantity > 5000 then 'A'
           when ticket_quantity > 1000 and ticket_quantity <= 5000 then 'B'
           when ticket_quantity > 500 and ticket_quantity <= 1000 then 'C'
           when ticket_quantity > 300 and ticket_quantity <= 500 then 'D'
           when ticket_quantity >= 0 and ticket_quantity <= 300 then 'E'
           else '其他'
    end                                                              as customer_level     --客户级别
     , ticket_quantity                                                                     --票量
     , equalflag                                                                           --标签,1代表新签 0代表非新签
     , sum(ticket_quantity) over (partition by date_time,agent_code) as total_ticket_num   --总客户数
     , count(customer_code) over (partition by date_time,agent_code) as total_customer_num --总票数
     , cast(date_time as string)                                     as dorisdt
from (
         select date_time                                                                                      --日期
              , agent_code                                                                                     --代理区名
              , agent_name                                                                                     --代理区名
              , customer_code                                                                                  --客户code
              , max(customer_name)                                                          as customer_name   --客户名称
              , customer_create_time                                                                           --客户签约时间
              , case when date_time = substr(customer_create_time, 1, 10) then 1 else 0 end as equalflag       --1代表新签 0代表非新签
              , sum(waybill_cnt)                                                            as ticket_quantity --票量
         from jms_dws.dws_customer_ticket_quantity_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -31)
           and dt <= '{{ execution_date | cst_ds }}'
           and agent_code is not null
           and network_name not like '%测试%'
         group by date_time     --日期
                , agent_code    --代理区名
                , agent_name    --代理区名
                , customer_code --客户code
                , customer_create_time --客户签约时间

     ) t1
union all
--日,代理区,加盟商
select 2                                                                       as networktype        --网点类型,3代理区,2加盟商,1网点
     , 1                                                                       as timetype           --时间类型,区分1日,2周,3月
     , date_time                                                                                     --日期
     , agent_code                                                                                    --代理区名
     , agent_name                                                                                    --代理区名
     , fran_code                                                                                     --加盟商code
     , fran_name                                                                                     --加盟商名
     , '--'                                                                    as network_code       --寄件网点
     , '--'                                                                    as network_name       --网点编码
     , customer_code                                                                                 --客户code
     , customer_name                                                                                 --客户名称
     , customer_create_time                                                                          --客户签约时间
     , customer_create_time                                                    as first_deliver_time --第一次发货时间
     , case
           when ticket_quantity > 5000 then 'A'
           when ticket_quantity > 1000 and ticket_quantity <= 5000 then 'B'
           when ticket_quantity > 500 and ticket_quantity <= 1000 then 'C'
           when ticket_quantity > 300 and ticket_quantity <= 500 then 'D'
           when ticket_quantity >= 0 and ticket_quantity <= 300 then 'E'
           else '其他'
    end                                                                        as customer_level     --客户级别
     , ticket_quantity                                                                               --票量
     , equalflag                                                                                     --标签,1代表新签 0代表非新签
     , sum(ticket_quantity) over (partition by date_time,agent_code,fran_code) as total_ticket_num   --总客户数
     , count(customer_code) over (partition by date_time,agent_code,fran_code) as total_customer_num --总票数
     , cast(date_time as string)                                               as dorisdt
from (
         select date_time                                                                                      --日期
              , agent_code                                                                                     --代理区名
              , agent_name                                                                                     --代理区名
              , fran_name                                                                                      --加盟商名
              , fran_code                                                                                      --加盟商code
              , customer_code                                                                                  --客户code
              , max(customer_name)                                                          as customer_name   --客户名称
              , customer_create_time                                                                           --客户签约时间
              , case when date_time = substr(customer_create_time, 1, 10) then 1 else 0 end as equalflag       --1代表新签 0代表非新签
              , sum(waybill_cnt)                                                            as ticket_quantity --票量
         from jms_dws.dws_customer_ticket_quantity_dt t
         where dt >= date_add('{{ execution_date | cst_ds }}', -31)
           and dt <= '{{ execution_date | cst_ds }}'
           and fran_code is not null
           and network_name not like '%测试%'
         group by date_time       --日期
                , agent_code      --代理区名
                , agent_name      --代理区名
                , fran_name       --加盟商名
                , fran_code       --加盟商code
                , customer_code   --客户code
                , t.customer_name --客户名称
                , customer_create_time
     ) t1
union all
--日,代理区,加盟商,网点
select 1                                                                     as networktype        --网点类型,3代理区,2加盟商,1网点
     , 1                                                                     as timetype           --时间类型,区分1日,2周,3月
     , date_time                                                                                   --日期
     , agent_code                                                                                  --代理区名
     , agent_name                                                                                  --代理区名
     , fran_code                                                                                   --加盟商code
     , fran_name                                                                                   --加盟商名
     , network_code                                                                                --寄件网点
     , network_name                                                                                --网点编码
     , customer_code                                                                               --客户code
     , customer_name                                                                               --客户名称
     , customer_create_time                                                                        --客户签约时间
     , customer_create_time                                                  as first_deliver_time --第一次发货时间
     , customer_level                                                                              --客户等级
     , waybill_cnt                                                           as ticket_quantuty    --票量
     , case
           when date_time = substr(customer_create_time, 1, 10) then 1
           else 0 end                                                        as equalflag          --1代表新签 0代表非新签
     , sum(waybill_cnt)
           over (partition by date_time,agent_code,fran_code,network_code)   as total_ticket_num   --总客户数
     , count(customer_code)
             over (partition by date_time,agent_code,fran_code,network_code) as total_customer_num --总票数
     , cast(date_time as string)                                             as dorisdt
from jms_dws.dws_customer_ticket_quantity_dt
where dt >= date_add('{{ execution_date | cst_ds }}', -31)
  and dt <= '{{ execution_date | cst_ds }}'
  and network_name not like '%测试%'
  and network_code is not null
union all
--周,代理区
select 3                                                                as networktype        --网点类型,3代理区,2加盟商,1网点
     , 2                                                                as timetype           --时间类型,区分1日,2周,3月
     , date_time                                                                              --日期
     , agent_code                                                                             --代理区名
     , agent_name                                                                             --代理区名
     , '--'                                                             as fran_code          --加盟商code
     , '--'                                                             as fran_name          --加盟商名
     , '--'                                                             as network_code       --寄件网点
     , '--'                                                             as network_name       --网点编码
     , customer_code                                                                          --客户code
     , customer_name                                                                          --客户名称
     , customer_create_time                                                                   --客户签约时间
     , customer_create_time                                             as first_deliver_time --第一次发货时间
     , case
           when ticket_quantity / weekday > 5000 then 'A'
           when ticket_quantity / weekday > 1000 and ticket_quantity / weekday <= 5000 then 'B'
           when ticket_quantity / weekday > 500 and ticket_quantity / weekday <= 1000 then 'C'
           when ticket_quantity / weekday > 300 and ticket_quantity / weekday <= 500 then 'D'
           when ticket_quantity / weekday >= 0 and ticket_quantity / weekday <= 300 then 'E'
           else '其他'
    end                                                                 as customer_level     --客户级别
     , round(ticket_quantity)                                           as ticket_quantity    --票量
     , equalflag                                                                              --标签,1代表新签 0代表非新签
     , sum(ticket_quantity) over (partition by date_time,t1.agent_code) as total_ticket_num   --总客户数
     , count(customer_code) over (partition by date_time,t1.agent_code) as total_customer_num --总票数
     , cast(monday_date as string)                                      as dorisdt
from (
         select date_time                           --日期
              , monday_date
              , agent_code                          --代理区名
              , agent_name                          --代理区名
              , customer_code                       --客户code
              , max(customer_name) as customer_name --客户名称
              , sum(waybill_cnt)   as ticket_quantity
              , customer_create_time                --客户签约时间
              , new_year_week                       --新签年周
              , weekday
              , case
                    when date_time = new_year_week
                        then 1
                    else 0
             end                   as equalflag     --1代表新签 0代表非新签
         from (
                  select concat(year_no, '-',
                                if(length(cast(week_no as string)) < 2, concat('0', week_no),
                                   week_no))                                             as date_time     --日期
                       , date_sub(date_time, pmod(datediff(date_time, '1900-01-08'), 7)) as monday_date
                       , agent_code                                                                       --代理区名
                       , agent_name                                                                       --代理区名
                       , customer_code                                                                    --客户code
                       , customer_name                                                                    --客户名称
                       , waybill_cnt
                       , customer_create_time                                                             --客户签约时间
                       , concat(year(next_day(date_add(customer_create_time, -7), 'MO')), '-',
                                if(length(cast(weekofyear(customer_create_time) as string)) < 2,
                                   concat('0', cast(weekofyear(customer_create_time) as string)),
                                   cast(weekofyear(customer_create_time) as string)))    as new_year_week --新签年周
                       , case
                             when concat(year_no, '-',
                                         if(length(cast(week_no as string)) < 2, concat('0', week_no),
                                            week_no)) =
                                  concat(year(next_day(DATE_ADD('{{ execution_date | cst_ds }}', -7), 'MO')), '-',
                                         if(length(cast(weekofyear('{{ execution_date | cst_ds }}') as string)) < 2,
                                            concat('0', cast(weekofyear('{{ execution_date | cst_ds }}') as string)),
                                            cast(weekofyear('{{ execution_date | cst_ds }}') as string)))
                                 then date_format('{{ execution_date | cst_ds }}', 'u')
                             else 7
                      end                                                                as weekday
                  from jms_dws.dws_customer_ticket_quantity_dt
                  where dt >=
                        date_sub('{{ execution_date | cst_ds }}', pmod(datediff('{{ execution_date | cst_ds }}', '1900-01-08'), 7) + 7+7+7+7)
                    and dt <= '{{ execution_date | cst_ds }}'
                    and network_name not like '%测试%'
                    and agent_code is not null
              ) t
         group by date_time            --日期
                , monday_date
                , agent_code           --代理区名
                , agent_name           --代理区名
                , customer_code        --客户code
                , customer_create_time --客户签约时间
                , new_year_week        --新签年周
                , weekday
     ) t1
union all
--周,代理区,加盟商
select 2                                                                             as networktype        --网点类型,3代理区,2加盟商,1网点
     , 2                                                                             as timetype           --时间类型,区分1日,2周,3月
     , date_time                                                                                           --日期
     , agent_code                                                                                          --代理区名
     , agent_name                                                                                          --代理区名
     , fran_code                                                                                           --加盟商code
     , fran_name                                                                                           --加盟商名
     , '--'                                                                          as network_code       --寄件网点
     , '--'                                                                          as network_name       --网点编码
     , customer_code                                                                                       --客户code
     , customer_name                                                                                       --客户名称
     , customer_create_time                                                                                --客户签约时间
     , customer_create_time                                                          as first_deliver_time --第一次发货时间
     , case
           when ticket_quantity / weekday > 5000 then 'A'
           when ticket_quantity / weekday > 1000 and ticket_quantity / weekday <= 5000 then 'B'
           when ticket_quantity / weekday > 500 and ticket_quantity / weekday <= 1000 then 'C'
           when ticket_quantity / weekday > 300 and ticket_quantity / weekday <= 500 then 'D'
           when ticket_quantity / weekday >= 0 and ticket_quantity / weekday <= 300 then 'E'
           else '其他'
    end                                                                              as customer_level     --客户级别
     , ticket_quantity                                                               as ticket_quantity    --票量
     , equalflag                                                                                           --标签,1代表新签 0代表非新签
     , sum(ticket_quantity) over (partition by date_time,t1.agent_code,t1.fran_code) as total_ticket_num   --总客户数
     , count(customer_code) over (partition by date_time,t1.agent_code,t1.fran_code) as total_customer_num --总票数
     , cast(monday_date as string)                                                   as dorisdt
from (
         select date_time                           --日期
              , monday_date
              , agent_code                          --代理区名
              , agent_name                          --代理区名
              , fran_name                           --加盟商名
              , fran_code                           --加盟商code
              , customer_code                       --客户code
              , max(customer_name) as customer_name --客户名称
              , sum(waybill_cnt)   as ticket_quantity
              , customer_create_time                --客户签约时间
              , new_year_week                       --新签年周
              , weekday
              , case
                    when date_time = new_year_week
                        then 1
                    else 0
             end                   as equalflag     --1代表新签 0代表非新签
         from (
                  select concat(year_no, '-',
                                if(length(cast(week_no as string)) < 2, concat('0', week_no),
                                   week_no))                                             as date_time     --日期
                       , date_sub(date_time, pmod(datediff(date_time, '1900-01-08'), 7)) as monday_date
                       , agent_code                                                                       --代理区名
                       , agent_name                                                                       --代理区名
                       , fran_name                                                                        --加盟商名
                       , fran_code                                                                        --加盟商code
                       , customer_code                                                                    --客户code
                       , customer_name                                                                    --客户名称
                       , waybill_cnt
                       , customer_create_time                                                             --客户签约时间
                       , concat(year(next_day(date_add(customer_create_time, -7), 'MO')), '-',
                                if(length(cast(weekofyear(customer_create_time) as string)) < 2,
                                   concat('0', cast(weekofyear(customer_create_time) as string)),
                                   cast(weekofyear(customer_create_time) as string)))    as new_year_week --新签年周
                       , case
                             when concat(year_no, '-',
                                         if(length(cast(week_no as string)) < 2, concat('0', week_no),
                                            week_no)) =
                                  concat(year(next_day(DATE_ADD('{{ execution_date | cst_ds }}', -7), 'MO')), '-',
                                         if(length(cast(weekofyear('{{ execution_date | cst_ds }}') as string)) < 2,
                                            concat('0', cast(weekofyear('{{ execution_date | cst_ds }}') as string)),
                                            cast(weekofyear('{{ execution_date | cst_ds }}') as string)))
                                 then date_format('{{ execution_date | cst_ds }}', 'u')
                             else 7
                      end                                                                as weekday
                  from jms_dws.dws_customer_ticket_quantity_dt
                  where dt >=
                        date_sub('{{ execution_date | cst_ds }}', pmod(datediff('{{ execution_date | cst_ds }}', '1900-01-08'), 7) + 7+7+7+7)
                    and dt <= '{{ execution_date | cst_ds }}'
                    and network_name not like '%测试%'
                    and fran_code is not null
              ) t
         group by date_time            --日期
                , monday_date
                , agent_code           --代理区名
                , agent_name           --代理区名
                , fran_name            --加盟商名
                , fran_code            --加盟商code
                , customer_code        --客户code
                , customer_create_time --客户签约时间
                , new_year_week        --新签年周
                , weekday
     ) t1
union all
--周,代理区,加盟商,网点
select 1                                                                              as networktype        --网点类型,3代理区,2加盟商,1网点
     , 2                                                                              as timetype           --时间类型,区分1日,2周,3月
     , date_time                                                                                            --日期
     , agent_code                                                                                           --代理区名
     , agent_name                                                                                           --代理区名
     , fran_code                                                                                            --加盟商code
     , fran_name                                                                                            --加盟商名
     , network_code                                                                                         --寄件网点
     , network_name                                                                                         --网点编码
     , customer_code                                                                                        --客户code
     , customer_name                                                                                        --客户名称
     , customer_create_time                                                                                 --客户签约时间
     , customer_create_time                                                           as first_deliver_time --第一次发货时间
     , case
           when ticket_quantity / weekday > 5000 then 'A'
           when ticket_quantity / weekday > 1000 and ticket_quantity / weekday <= 5000 then 'B'
           when ticket_quantity / weekday > 500 and ticket_quantity / weekday <= 1000 then 'C'
           when ticket_quantity / weekday > 300 and ticket_quantity / weekday <= 500 then 'D'
           when ticket_quantity / weekday >= 0 and ticket_quantity / weekday <= 300 then 'E'
           else '其他'
    end                                                                               as customer_level     --客户级别
     , ticket_quantity                                                                                      --票量
     , equalflag                                                                                            --标签,1代表新签 0代表非新签
     , sum(ticket_quantity)
           over (partition by date_time,t1.agent_code,t1.fran_code,t1.network_code)   as total_ticket_num   --总客户数
     , count(customer_code)
             over (partition by date_time,t1.agent_code,t1.fran_code,t1.network_code) as total_customer_num --总票数
     , cast(monday_date as string)                                                    as dorisdt
from (
         select date_time                           --日期
              , monday_date
              , agent_code                          --代理区名
              , agent_name                          --代理区名
              , fran_name                           --加盟商名
              , fran_code                           --加盟商code
              , network_name                        --寄件网点
              , network_code                        --网点编码
              , customer_code                       --客户code
              , max(customer_name) as customer_name --客户名称
              , sum(waybill_cnt)   as ticket_quantity
              , customer_create_time                --客户签约时间
              , new_year_week                       --新签年周
              , weekday
              , case
                    when date_time = new_year_week
                        then 1
                    else 0
             end                   as equalflag     --1代表新签 0代表非新签
         from (
                  select concat(year_no, '-',
                                if(length(cast(week_no as string)) < 2, concat('0', week_no),
                                   week_no))                                             as date_time     --日期
                       , date_sub(date_time, pmod(datediff(date_time, '1900-01-08'), 7)) as monday_date
                       , agent_code                                                                       --代理区名
                       , agent_name                                                                       --代理区名
                       , fran_name                                                                        --加盟商名
                       , fran_code                                                                        --加盟商code
                       , network_name                                                                     --寄件网点
                       , network_code                                                                     --网点编码
                       , customer_code                                                                    --客户code
                       , customer_name                                                                    --客户名称
                       , waybill_cnt
                       , customer_create_time                                                             --客户签约时间
                       , concat(year(next_day(date_add(customer_create_time, -7), 'MO')), '-',
                                if(length(cast(weekofyear(customer_create_time) as string)) < 2,
                                   concat('0', cast(weekofyear(customer_create_time) as string)),
                                   cast(weekofyear(customer_create_time) as string)))    as new_year_week --新签年周
                       , case
                             when concat(year_no, '-',
                                         if(length(cast(week_no as string)) < 2, concat('0', week_no),
                                            week_no)) =
                                  concat(year(next_day(DATE_ADD('{{ execution_date | cst_ds }}', -7), 'MO')), '-',
                                         if(length(cast(weekofyear('{{ execution_date | cst_ds }}') as string)) < 2,
                                            concat('0', cast(weekofyear('{{ execution_date | cst_ds }}') as string)),
                                            cast(weekofyear('{{ execution_date | cst_ds }}') as string)))
                                 then date_format('{{ execution_date | cst_ds }}', 'u')
                             else 7
                      end                                                                as weekday
                  from jms_dws.dws_customer_ticket_quantity_dt
                  where dt >=
                        date_sub('{{ execution_date | cst_ds }}', pmod(datediff('{{ execution_date | cst_ds }}', '1900-01-08'), 7) + 7+7+7+7)
                    and dt <= '{{ execution_date | cst_ds }}'
                    and network_name not like '%测试%'
                    and network_code is not null
              ) t
         group by date_time            --日期
                , monday_date
                , agent_code           --代理区名
                , agent_name           --代理区名
                , fran_name            --加盟商名
                , fran_code            --加盟商code
                , network_name         --寄件网点
                , network_code         --网点编码
                , customer_code        --客户code
                , customer_create_time --客户签约时间
                , new_year_week        --新签年周
                , weekday
     ) t1
union all
--月,代理区
select 3                                                                as networktype        --网点类型,3代理区,2加盟商,1网点
     , 3                                                                as timetype           --时间类型,区分1日,2周,3月
     , date_time                                                                              --日期
     , agent_code                                                                             --代理区名
     , agent_name                                                                             --代理区名
     , '--'                                                             as fran_code          --加盟商code
     , '--'                                                             as fran_name          --加盟商名
     , '--'                                                             as network_code       --寄件网点
     , '--'                                                             as network_name       --网点编码
     , customer_code                                                                          --客户code
     , customer_name                                                                          --客户名称
     , customer_create_time                                                                   --客户签约时间
     , customer_create_time                                             as first_deliver_time --第一次发货时间
     , case
           when ticket_quantity / day_month_num > 5000 then 'A'
           when ticket_quantity / day_month_num > 1000 and
                ticket_quantity / day_month_num <= 5000 then 'B'
           when ticket_quantity / day_month_num > 500 and
                ticket_quantity / day_month_num <= 1000 then 'C'
           when ticket_quantity / day_month_num > 300 and
                ticket_quantity / day_month_num <= 500 then 'D'
           when ticket_quantity / day_month_num >= 0 and ticket_quantity / day_month_num <= 300
               then 'E'
           else '其他'
    end                                                                 as customer_level     --客户级别
     , ticket_quantity                                                                        --票量
     , case
           when substr(customer_create_time, 1, 7) = date_time
               then 1
           else 0
    end                                                                 as equalflag          --1代表新签 0代表非新签
     , sum(ticket_quantity) over (partition by date_time,t1.agent_code) as total_ticket_num   --总客户数
     , count(customer_code) over (partition by date_time,t1.agent_code) as total_customer_num --总票数
     , concat(date_time, '-', '01')                                     as dorisdt
from (
         select date_time                             --日期
              , agent_code                            --代理区名
              , agent_name                            --代理区名
              , customer_code                         --客户code
              , max(customer_name) as customer_name   --客户名称
              , customer_create_time                  --客户签约时间
              , day_month_num
              , sum(waybill_cnt)   as ticket_quantity --票量
         from (
                  select substr(date_time, 1, 7) as date_time
                       , agent_code           --代理区名
                       , agent_name           --代理区名
                       , customer_code        --客户code
                       , customer_name        --客户名称
                       , customer_create_time --客户签约时间
                       , waybill_cnt
                       , case
                             when substr(date_time, 1, 7) = substr('{{ execution_date | cst_ds }}', 1, 7)
                                 then dayofmonth('{{ execution_date | cst_ds }}')
                             else datediff(add_months(concat(substr(date_time, 1, 7), '-', '01'), 1),
                                           concat(substr(date_time, 1, 7), '-', '01'))
                      end                        as day_month_num
                  from jms_dws.dws_customer_ticket_quantity_dt
                  where dt >= trunc(add_months('{{ execution_date | cst_ds }}', -1), 'MM')
                    and dt <= '{{ execution_date | cst_ds }}'
                    and agent_code is not null
                    and network_name not like '%测试%'
                    and customer_name not like 'BEST%'
              ) t
         group by date_time
                , agent_code           --代理区名
                , agent_name           --代理区名
                , customer_code        --客户code
                , customer_create_time --客户签约时间
                , day_month_num
     ) t1
union all
--月,代理区,加盟商
select 2                                                                             as networktype        --网点类型,3代理区,2加盟商,1网点
     , 3                                                                             as timetype           --时间类型,区分1日,2周,3月
     , date_time                                                                                           --日期
     , agent_code                                                                                          --代理区名
     , agent_name                                                                                          --代理区名
     , fran_code                                                                                           --加盟商code
     , fran_name                                                                                           --加盟商名
     , '--'                                                                          as network_code       --寄件网点
     , '--'                                                                          as network_name       --网点编码
     , customer_code                                                                                       --客户code
     , customer_name                                                                                       --客户名称
     , customer_create_time                                                                                --客户签约时间
     , customer_create_time                                                          as first_deliver_time --第一次发货时间
     , case
           when ticket_quantity / day_month_num > 5000 then 'A'
           when ticket_quantity / day_month_num > 1000 and
                ticket_quantity / day_month_num <= 5000 then 'B'
           when ticket_quantity / day_month_num > 500 and
                ticket_quantity / day_month_num <= 1000 then 'C'
           when ticket_quantity / day_month_num > 300 and
                ticket_quantity / day_month_num <= 500 then 'D'
           when ticket_quantity / day_month_num >= 0 and ticket_quantity / day_month_num <= 300
               then 'E'
           else '其他'
    end                                                                              as customer_level     --客户级别
     , ticket_quantity                                                                                     --票量
     , case
           when substr(customer_create_time, 1, 7) = substr(date_time, 1, 7) then 1
           else 0 end                                                                as equalflag          --1代表新签 0代表非新签
     , sum(ticket_quantity) over (partition by date_time,t1.agent_code,t1.fran_code) as total_ticket_num   --总客户数
     , count(customer_code) over (partition by date_time,t1.agent_code,t1.fran_code) as total_customer_num --总票数
     , concat(date_time, '-', '01')                                                  as dorisdt
from (
         select date_time                             --日期
              , agent_code                            --代理区名
              , agent_name                            --代理区名
              , fran_code
              , fran_name
              , customer_code                         --客户code
              , max(customer_name) as customer_name   --客户名称
              , customer_create_time                  --客户签约时间
              , day_month_num
              , sum(waybill_cnt)   as ticket_quantity --票量
         from (
                  select substr(date_time, 1, 7) as date_time
                       , agent_code           --代理区名
                       , agent_name           --代理区名
                       , fran_code
                       , fran_name
                       , customer_code        --客户code
                       , customer_name        --客户名称
                       , customer_create_time --客户签约时间
                       , waybill_cnt
                       , case
                             when substr(date_time, 1, 7) = substr('{{ execution_date | cst_ds }}', 1, 7)
                                 then dayofmonth('{{ execution_date | cst_ds }}')
                             else datediff(add_months(concat(substr(date_time, 1, 7), '-', '01'), 1),
                                           concat(substr(date_time, 1, 7), '-', '01'))
                      end                        as day_month_num
                  from jms_dws.dws_customer_ticket_quantity_dt
                  where dt >= trunc(add_months('{{ execution_date | cst_ds }}', -1), 'MM')
                    and dt <= '{{ execution_date | cst_ds }}'
                    and agent_code is not null
                    and network_name not like '%测试%'
                    and customer_name not like 'BEST%'
              ) t
         group by date_time
                , agent_code           --代理区名
                , agent_name           --代理区名
                , fran_code
                , fran_name
                , customer_code        --客户code
                , customer_create_time --客户签约时间
                , day_month_num
     ) t1
union all
--月,代理区,加盟商,网点
select 1                                                                                    as networktype        --网点类型,3代理区,2加盟商,1网点
     , 3                                                                                    as timetype           --时间类型,区分1日,2周,3月
     , date_time                                                                                                  --日期
     , agent_code                                                                                                 --代理区名
     , agent_name                                                                                                 --代理区名
     , fran_code                                                                                                  --加盟商code
     , fran_name                                                                                                  --加盟商名
     , network_code                                                                                               --寄件网点
     , network_name                                                                                               --网点编码
     , customer_code                                                                                              --客户code
     , customer_name                                                                                              --客户名称
     , customer_create_time                                                                                       --客户签约时间
     , customer_create_time                                                                 as first_deliver_time --第一次发货时间
     , case
           when ticket_quantity / day_month_num > 5000 then 'A'
           when ticket_quantity / day_month_num > 1000 and
                ticket_quantity / day_month_num <= 5000 then 'B'
           when ticket_quantity / day_month_num > 500 and
                ticket_quantity / day_month_num <= 1000 then 'C'
           when ticket_quantity / day_month_num > 300 and
                ticket_quantity / day_month_num <= 500 then 'D'
           when ticket_quantity / day_month_num >= 0 and ticket_quantity / day_month_num <= 300
               then 'E'
           else '其他'
    end                                                                                     as customer_level     --客户级别
     , ticket_quantity                                                                                            --票量
     , case
           when substr(customer_create_time, 1, 7) = substr(date_time, 1, 7) then 1
           else 0 end                                                                       as equalflag          --1代表新签 0代表非新签
     , sum(ticket_quantity) over (partition by date_time,agent_code,fran_code,network_code) as total_ticket_num   --总客户数
     , count(customer_code) over (partition by date_time,agent_code,fran_code,network_code) as total_customer_num --总票数
     , concat(date_time, '-', '01')                                                         as dorisdt
from (
         select date_time                             --日期
              , agent_code                            --代理区名
              , agent_name                            --代理区名
              , fran_code
              , fran_name
              , network_code
              , network_name
              , customer_code                         --客户code
              , max(customer_name) as customer_name   --客户名称
              , customer_create_time                  --客户签约时间
              , day_month_num
              , sum(waybill_cnt)   as ticket_quantity --票量
         from (
                  select substr(date_time, 1, 7) as date_time
                       , agent_code           --代理区名
                       , agent_name           --代理区名
                       , fran_code
                       , fran_name
                       , network_code
                       , network_name
                       , customer_code        --客户code
                       , customer_name        --客户名称
                       , customer_create_time --客户签约时间
                       , waybill_cnt
                       , case
                             when substr(date_time, 1, 7) = substr('{{ execution_date | cst_ds }}', 1, 7)
                                 then dayofmonth('{{ execution_date | cst_ds }}')
                             else datediff(add_months(concat(substr(date_time, 1, 7), '-', '01'), 1),
                                           concat(substr(date_time, 1, 7), '-', '01'))
                      end                        as day_month_num
                  from jms_dws.dws_customer_ticket_quantity_dt
                  where dt >= trunc(add_months('{{ execution_date | cst_ds }}', -1), 'MM')
                    and dt <= '{{ execution_date | cst_ds }}'
                    and agent_code is not null
                    and network_name not like '%测试%'
                    and customer_name not like 'BEST%'
              ) t
         group by date_time
                , agent_code           --代理区名
                , agent_name           --代理区名
                , fran_code
                , fran_name
                , network_code
                , network_name
                , customer_code        --客户code
                , customer_create_time --客户签约时间
                , day_month_num
     ) t1
;
